﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.IO;
using System.Data.Common;

namespace MISAPP.Common
{
    /// <summary>
    /// 和資料庫溝通相關的程式在這
    /// </summary>
    public class DAL2
    {        
        protected static DbProviderFactory Factory = null;
        protected static DbConnection _connection = null;
        protected static DbConnection Connection
        {
            get
            {
                if (_connection == null)
                {
                    Factory = DbProviderFactories.GetFactory(GetDBProvider());
                    _connection = Factory.CreateConnection(); //new System.Data.SqlClient.SqlConnection(ConnectionString);
                    _connection.ConnectionString = ConnectionString;
                }
                return _connection;
            }
        }

        public static DbCommand GetCommand()
        {
            return Connection.CreateCommand();
        }


        protected static string GetDBProvider()
        {
            string configFile = "Common.dll";
            Configuration config = null;
            if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + configFile)) //如果這一層沒有，就再加bin
            {
                config = ConfigurationManager.OpenExeConfiguration(configFile);
            }
            else
            {
                config = ConfigurationManager.OpenExeConfiguration(AppDomain.CurrentDomain.BaseDirectory + @"bin\" + configFile);
            }
            ConnectionString = config.ConnectionStrings.ConnectionStrings["MSSQL"].ToString();
            return config.ConnectionStrings.ConnectionStrings["MSSQL"].ProviderName.ToString();
        }


        /// <summary>
        /// 取回連線字串，要注意的是字串名稱為
        /// </summary>
        /// 
        protected static String ConnectionString = "";
        

        /// <summary>
        /// 回傳 IDbDataParameter
        /// </summary>
        /// <param name="p_name">欄位名</param>
        /// <param name="p_dbType">欄位型態</param>
        /// <param name="p_value">值</param>
        /// <returns>IDbDataParameter</returns>
        public static DbParameter GetParameter(string p_name, DbType p_dbType, object p_value, DbParameter p_para)
        {
            p_para.ParameterName = p_name;
            p_para.DbType = p_dbType;
            p_para.Value = p_value;
            return p_para;
        }

        /// <summary>
        /// 要傳入的 Command 要先把Parameter 組好
        /// </summary>
        /// <param name="p_cmd">IDBCommand</param>
        /// <returns>int</returns>
        public static int ExecuteNoneQuery(DbCommand p_cmd)
        {
            int iResult = -1;
            try
            {
                //ConnectionOpenClose();
                ConnectionOpen();
                iResult = p_cmd.ExecuteNonQuery();

            }
            catch (DbException ex)
            {
                //TODO
                ExceptionHandle(ex);
            }
            catch (Exception ex)
            {
                //TODO
                ExceptionHandle(ex);
            }
            finally
            {
                Connection.Close();
            }
            return iResult;
        }

        /// <summary>
        /// 如果沒有打開，那就開了，如果已開了就不用開了^^
        /// </summary>
        protected static void ConnectionOpen()
        {
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
            }
        }

        /// <summary>
        /// 如果關了就開，開了就關！
        /// </summary>
        //protected static void ConnectionOpenClose()
        //{
        //    if (Connection.State == ConnectionState.Closed)
        //    {
        //        Connection.Open();
        //    }
        //    else
        //    {
        //        Connection.Close();
        //    }            
        //}

        public static IDataReader GetDataReader(IDbCommand p_cmd)
        {
            //DbDataReader reader = null;
            IDataReader reader = null;
            try
            {
                //ConnectionOpenClose();
                if (Connection.State == ConnectionState.Closed)
                {
                    Connection.Open();
                }                
                reader = p_cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (System.Data.Common.DbException ex)
            {
                ExceptionHandle(ex);
            }
            catch (Exception ex)
            {
                ExceptionHandle(ex);
            }
            finally
            {
               // Connection.Close();
            }
            return reader;
        }

        public static DataSet GetDataSet(DbCommand p_cmd)
        {

            DataSet ds = new DataSet();
            try
            {
                //ConnectionOpenClose();
                ConnectionOpen();
                DbDataAdapter da = GetDataAdapter(p_cmd);
                da.Fill(ds);
            }
            catch (System.Data.Common.DbException ex)
            {
                ExceptionHandle(ex);
            }
            catch (Exception ex)
            {
                ExceptionHandle(ex);
            }
            finally
            {
                //ConnectionOpenClose();
                Connection.Close();
            }
            return ds;
        }

        //public static DataTable GetDataTable(DbCommand p_cmd)
        public static DataTable GetDataTable(IDbCommand p_cmd)
        {
            DataTable dt = new DataTable();
            try
            {
                dt.Load(GetDataReader(p_cmd));
            }
            catch (System.Data.Common.DbException ex)
            {
                ExceptionHandle(ex);
            }
            catch (Exception ex)
            {
                ExceptionHandle(ex);
            }
            finally
            {

            }
            return dt;
        }

        /// <summary>
        /// 在這實體化 DataAdapter
        /// </summary>
        /// <returns></returns>
        protected static DbDataAdapter GetDataAdapter(DbCommand p_cmd)
        {
            DbDataAdapter da = Factory.CreateDataAdapter();
            da.SelectCommand = p_cmd;
            return da;
            //System.Data.SqlClient.SqlCommand cmd = (System.Data.SqlClient.SqlCommand)p_cmd;
            //return new System.Data.SqlClient.SqlDataAdapter(cmd);
        }

        protected static void ExceptionHandle(Exception ex)
        {
            //TODO 這未來要做什麼
            //Utilitys.ExceptionTOLog(ex);
        }
    }
}


//System.Data.OracleClient.OracleConnection cn = new System.Data.OracleClient.OracleConnection();
//            cn.ConnectionString = "Data Source=FUBON_ODSDB;User ID=T_SAINTCHANG;Password=1qaz2wsx;";
//            cn.Open();
//            System.Data.OracleClient.OracleCommand cmd = cn.CreateCommand();
//            cmd.CommandText = "SELECT SYSDATE FROM DUAL";
//            System.Data.OracleClient.OracleDataReader dr = cmd.ExecuteReader();
//            System.Data.DataTable dt = new System.Data.DataTable();
//            dt.Load(dr);